package com.dream.wukong.common.core.jdbc;

import com.dream.wukong.common.core.cache.ehcache.EhcacheCache;
import com.dream.wukong.common.core.interfaces.cache.ICacheService;
import com.dream.wukong.common.core.interfaces.dao.PageResult;
import com.dream.wukong.common.core.interfaces.jdbc.INativeQuery;
import com.dream.wukong.common.core.jdbc.dialect.Dialect;
import com.dream.wukong.common.core.jdbc.sqlconfig.SqlMapper;
import com.dream.wukong.common.core.jdbc.sqltemplate.SqlTemplate;
import com.dream.wukong.common.core.jdbc.sqltemplate.impl.VelocitySqlTemplate;
import com.dream.wukong.common.core.util.EncryptUtil;
import com.dream.wukong.common.core.util.MapUtils;
import com.dream.wukong.common.core.util.StringUtil;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterUtils;
import org.springframework.jdbc.core.namedparam.ParsedSql;
import org.springframework.util.StringUtils;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @Author: wuyichao
 * @Description:
 * @Date: Created in 10:08 2018/9/18.
 * @Modified:
 */
public class JpaNativeQuery implements INativeQuery {
    private static Log log = LogFactory
            .getLog(JpaNativeQuery.class);


    private static int DEFAULT_PAGE_SIZE = 50;

    protected ICacheService dataCache = new EhcacheCache(getClass().getName());

    private SqlTemplate sqlTemplate = new VelocitySqlTemplate();

    /**
     * SPRING 获取JPA entityManager的两种方式
     * LocalContainerEntityManagerFactoryBean entityManagerFactory = ctx.getBean(LocalContainerEntityManagerFactoryBean.class);
     * entityManager = entityManagerFactory.nativeEntityManagerFactory.createEntityManager();
     * <p>
     * EntityManager entityManager = ctx.getBean(EntityManager.class);
     * System.out.println(entityManager);
     */
    private EntityManager entityManager;

    /**
     * @param map
     * @return
     * @throws Exception
     */
    public List<Map<String, Object>> selectAllList(String sqlKey,
                                                   Map<String, Object> map) {
        String cacheKey = getCacheKey(sqlKey, map);
        SqlMapper sqlMapper = sqlTemplate.getSqlMapper(sqlKey);
        String sql = sqlTemplate.getSql(sqlKey, map);
        if (sqlMapper == null) {
            throw new RuntimeException("load sqlconfig error :" + sqlKey);
        }
        if (sqlMapper.isFlushCache()) {
            dataCache.delete(cacheKey);
        }
        if (sqlMapper.isUseCache()) {
            if (dataCache.get(cacheKey) != null) {
                log.debug("load from cacheKey : " + cacheKey);
                return (List<Map<String, Object>>) dataCache
                        .get(cacheKey);
            } else {
                log.debug("reload   cacheKey : " + cacheKey);
                List<Map<String, Object>> rs = this.queryForList(sql, map);
                dataCache.put(cacheKey, rs);
                return rs;
            }
        } else {
            List<Map<String, Object>> rs = this.queryForList(sql, map);
            return rs;
        }
    }

    /**
     * @param sqlKey
     * @param map
     * @param offset
     * @param limit
     * @return offset+1~offset+limit
     */
    public List<Map<String, Object>> selectPageList(String sqlKey,
                                                    Map<String, Object> map, int offset, int limit) {
        log.debug("offset " + offset + " limit " + limit);
        String sql = sqlTemplate.getSql(sqlKey, map);
        return queryPageList(sql, map, offset, limit);
    }


    /**
     * @param
     * @param map
     * @param offset
     * @param limit
     * @return offset+1~offset+limit
     */
    public List<Map<String, Object>> selectLogicPageList(String sql,
                                                         Map<String, Object> map, int offset, int limit) {
        log.debug("offset " + offset + " limit " + limit);
        log.debug(sql);
        List<Map<String, Object>> rsList = new ArrayList<Map<String, Object>>(
                limit);
        List<Map<String, Object>> ls = this.queryForList(sql, map);
        // offset+1~offset+limit
        for (int rownum = 0; rownum < ls.size() && rownum <= offset + limit; rownum++) {
            log.debug(ls.get(rownum));
            if (rownum >= offset + 1 && rownum <= offset + limit) {
                log.debug("add ...." + ls.get(rownum));
                rsList.add(ls.get(rownum));
            }
        }
        return rsList;
    }

    /**
     * @param sqlkey
     * @param map
     * @return
     */
    public List<Map<String, Object>> selectPageList(String sqlkey,
                                                    Map<String, Object> map) {
        MapUtils<String, Object> rm = new MapUtils<String, Object>(map);
        int offset = rm.getInt(Dialect.OFFSET);
        int limit = rm.hasKey(Dialect.LIMIT) ? rm.getInt(Dialect.LIMIT)
                : DEFAULT_PAGE_SIZE;
        return this.selectPageList(sqlkey, map, offset, limit);
    }

    /**
     * @param map
     * @return
     * @throws Exception
     */
    public Long selectCount(String sqlKey, Map<String, Object> map) {
        String sql = String.format("select count(1) from ( %s )", sqlTemplate.getSql(sqlKey, map));
        return this.queryForObject(sql, map, Long.class);
    }


    public String getSql(String sqlKey, Map<String, Object> map) {
        String sql = sqlTemplate.getSql(sqlKey, map);
        return "[" + sqlKey + "]" + getExecuteSql(sql, map);
    }

    public String getExecuteSql(String sql, Map<String, Object> map) {
        String convertSql = sqlConvert(sql, map);

        ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(sql);
        MapSqlParameterSource paramSource = new MapSqlParameterSource(map);
        String sqlToUse = NamedParameterUtils.substituteNamedParameters(
                parsedSql, paramSource);
        Object[] params = NamedParameterUtils.buildValueArray(parsedSql,
                paramSource, null);
        List<Object> paramList = Arrays.asList(params);
        String executeSql = getSql(sqlToUse, paramList);
        log.debug("sql :" + sql);
        log.debug("convertSql :" + convertSql);
        log.debug("parsedSql :" + parsedSql);
        log.debug("sqlToUse :" + sqlToUse);
        log.debug("params :" + paramList);
        log.debug("executeSql :" + executeSql);
        return executeSql;
    }

    /**
     * sql ( select * from table where user_id = :userId ) Convert to standard
     * jdbc sql ( select * from table where user_id = ?) ;
     *
     * @param sql
     * @param map
     * @param list
     * @return
     */
    @SuppressWarnings("all")
    private String sqlConvert(String sql, Map qryMap) {
        // replace #param to value
        if (qryMap != null && !qryMap.isEmpty()) {
            List<String> params = getReplaceParameterList(sql);
            for (String param : params) {
                sql = StringUtil.replaceOnce(sql, "#" + param,
                        StringUtil.asString(qryMap.get(param)));
            }
        }
        return sql;
    }

    private String getSql(String sql, List<Object> lstParam) {
        for (int i = 0; i < lstParam.size(); i++) {
            Object value = lstParam.get(i);
            if (value instanceof Integer || value instanceof Double
                    || value instanceof Float || value instanceof Long) {
                sql = StringUtil.replaceOnce(sql, "?", value.toString());
            } else if (value instanceof String) {
                sql = StringUtil.replaceOnce(sql, "?", "'" + value.toString()
                        + "'");
            } else {
                sql = StringUtil.replaceOnce(sql, "?",
                        "'" + String.valueOf(value) + "'");
            }
        }
        return sql;
    }


    /**
     * sql ( select * from table where user_id = #userId ) #userId will be
     * replaced into list as param
     *
     * @param sql
     * @return
     */
    private List<String> getReplaceParameterList(String sql) {
        String regex = "\\#(\\w+)";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(sql);
        List<String> params = new ArrayList<String>();
        while (matcher.find())
            params.add(matcher.group(1));
        return params;
    }

    @Override
    public List<Map<String, Object>> queryForList(String sql,
                                                  Map<String, Object> map) {
        Query query = getSQLQuery(sql, map);
        return query.getResultList();
    }

    @Override
    public Map<String, Object> queryForMap(String sql, Map<String, Object> paramMap) {
        Query query = getSQLQuery(sql, paramMap);
        return (Map<String, Object>) query.getSingleResult();
    }

    @Override
    public <T> T queryForObject(String sql, Map<String, Object> map,
                                Class<T> requiredType) {
        Query query = entityManager.createNativeQuery(sql);
        for (String key : map.keySet())
            query.setParameter(key, map.get(key));
        return requiredType.cast(query.getSingleResult());
    }


    /**
     * 使用Hibernate内置的分页查询功能
     *
     * @param sql
     * @param map
     * @param offset
     * @param limit
     * @return
     */
    private List<Map<String, Object>> queryPageList(String sql,
                                                    Map<String, Object> map, int offset, int limit) {
        Query query = getSQLQuery(sql, map);
        query.setFirstResult(offset);// 设置查询结果的开始记录数
        query.setMaxResults(offset + limit);// 设查询结果的结束记录数 查询记录数1~~10的数据
        List<Map<String, Object>> rows = query.getResultList();
        return rows;
    }

    private Query getSQLQuery(String sql,
                              Map<String, ?> map) {
        Query query = entityManager.createNativeQuery(sql);

        for (String key : map.keySet())
            query.setParameter(key, map.get(key));
        query.unwrap(SQLQuery.class).setResultTransformer(
                Transformers.ALIAS_TO_ENTITY_MAP);
        return query;
    }


    public void setDataCache(ICacheService dataCache) {
        log.debug("setDataCache" + dataCache);
        this.dataCache = dataCache;
    }

    public void setSqlTemplate(SqlTemplate sqlTemplate) {
        this.sqlTemplate = sqlTemplate;
    }

    public JpaNativeQuery() {
        super();
    }


    @Override
    public List<Map<String, Object>> selectPageListBySql(String sql,
                                                         Map<String, Object> map, int offset, int limit) {
        return queryPageList(sql, map, offset, limit);
    }


    private Query getHqlQuery(String hql, Object param) {
        Query query = entityManager.createQuery(hql);
//		query.setResultTransformer(BeanTransformer.aliasToBean(beanClz));
//		prepareQuery(query);
        if (param != null) {
            if (Map.class.isAssignableFrom(param.getClass())) {
                Map<String, Object> values = (Map<String, Object>) param;
                for (Map.Entry<String, ?> entry : values.entrySet()) {
                    if (hql.indexOf("=:" + entry.getKey()) > 0 || hql.indexOf("= :" + entry.getKey()) > 0) {
                        query.setParameter(entry.getKey(), entry.getValue());
                    }
                }
            } else {
                Object[] values = (Object[]) param;
                for (int i = 0; i < values.length; i++) {
                    query.setParameter(i + 1, values[i]);
                }
            }
        }
        return query;
    }

    @Override
    public Long selectCountBySql(String sql, Map<String, Object> map) {
        return null;
    }

    @Override
    public List selectPageListBySql(String hql, Map map, int offset, int limit,
                                    Class cls) {
        Query query = getHqlQuery(hql, map);
        query.setFirstResult(offset);// 设置查询结果的开始记录数
        query.setMaxResults(offset + limit);// 设查询结果的结束记录数 查询记录数1~~10的数据
        List<Map<String, Object>> rows = query.getResultList();
        return getHqlQuery(hql, map).getResultList();
    }


    private <T> PageResult<T> getPageResult(Query queryObject, PageResult<T> pageResult) {
        int totalCount = queryObject.getResultList().size();
        pageResult.setTotal(totalCount);
        int start = (int) ((pageResult.getPageNo() - 1) * pageResult.getPageSize());
        queryObject.setFirstResult(start);
        queryObject.setMaxResults(pageResult.getPageSize());
        pageResult.setRows(queryObject.getResultList());
        return pageResult;
    }

    @Override
    public <T> PageResult<T> selectPageResult(String sqlKey,
                                              Map<String, Object> map, PageResult<T> pageResult, Class<T> cls) {
        return null;
    }

    //TODO
    @Override
    public <T> PageResult<T> selectPageResultBySql(String hql,
                                                   Map<String, Object> map, PageResult<T> pageResult, Class<T> cls) {
        return getPageResult(getHqlQuery(hql, map), pageResult);
    }

    private String getCacheKey(String sqlKey, Map<String, Object> qryMap) {
        StringBuffer cacheKey = new StringBuffer(CACHE_KEY_PREFIX + sqlKey);
        SortedMap<String, Object> sortParams = new TreeMap<String, Object>(
                qryMap);
        List<String> paramList = new ArrayList<String>(4);
        for (String paramKey : sortParams.keySet()) {
            String value = StringUtil.asString(sortParams.get(paramKey));
            if ("sqlKey".equals(paramKey) || !StringUtils.hasLength(value)) {
                continue;
            }
            paramList.add(paramKey + "=" + value);
        }
        //如果参数列表不为空，MD5编码后以_分割；
        if (!paramList.isEmpty()) {
            String str1 = StringUtils.collectionToDelimitedString(paramList, "&");
            String paramStr = EncryptUtil.encodeStr(str1).toUpperCase();
            cacheKey.append("_").append(paramStr);
        }
        return cacheKey.toString();
    }
}
